package com.lenovo.export;

import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.CellUtil;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.HColumnDescriptor;
import org.apache.hadoop.hbase.HTableDescriptor;
import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.Admin;
import org.apache.hadoop.hbase.client.Connection;
import org.apache.hadoop.hbase.client.ConnectionFactory;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.ResultScanner;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.client.Table;
import org.apache.hadoop.hbase.util.Bytes;

/**
 *
 * @author lucl
 *         HBase的配置实例
 *
 */
public class HBaseManagerMain {
    private static final Log LOG = LogFactory.getLog(HBaseManagerMain.class);
    // 在Eclipse中运行时报错如下
    // Caused by: java.lang.ClassNotFoundException: org.apache.htrace.Trace
    // Caused by: java.lang.NoClassDefFoundError: io/netty/channel/ChannelHandler
    // 需要把单独的htrace-core-3.1.0-incubating.jar和netty-all-4.0.5.final.jar导入项目中

    private static final String TABLE_NAME = "upp:upp_user_profile";
    private static final String COLUMN_FAMILY_NAME = "nature";

    /**
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        Configuration conf = HBaseConfiguration.create();
        //conf.set("hbase.master", "10.120.193.73");
        //        conf.set("hbase.master.port", "60000");
        //        conf.set("hbase.zookeeper.property.clientport", "2181");
        //        conf.set("hbase.zookeeper.quorum", "lenovo");
        // conf.set("hbase.zookeeper.quorum", "10.120.193.81");
        //conf.set("zookeeper.znode.parent", "/hbase-unsecure");
        conf.set("hbase.master.port", "60000");
        conf.set("hbase.zookeeper.property.clientport", "2181");
        conf.set("hbase.zookeeper.quorum", "node73.it.leap.com,node75.it.leap.com,node76.it.leap.com,node81.it.leap.com,node82.it.leap.com");
        // conf.set("hbase.zookeeper.quorum", "10.120.193.81");
        conf.set("zookeeper.znode.parent", "/hbase-unsecure");
        HBaseManagerMain manageMain = new HBaseManagerMain();
        try {
            /**
             * HTable类读写时是非线程安全的，已经标记为Deprecated
             * 建议通过org.apache.hadoop.hbase.client.Connection来获取实例
             */
            Connection connection = ConnectionFactory.createConnection(conf);

            /**
             * 列出所有的表
             */
            // manageMain.listTables(admin);

            /**
             * 添加数据
             */
            // manageMain.putDatas(connection);

            List<UserProfile> userlist = manageMain.scanTable(connection);

            java.sql.Connection con =DriverManager.getConnection("jdbc:mysql://10.96.91.186:3306/lenovo_upp_test?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8" ,"root" , "123456");
            con.setAutoCommit(true);
            String sql = "REPLACE INTO tb_user_profile(user_name,employee_type,working_years,band,country,city,bu,pcg_dcg,segment,is_new_user,title_change,manager,is_rol_user,is_monthly_user,kpi_keyword,asset_name,function,app_ticket_num,not_app_ticket_num,ticket_type,ticket_distribution,peak_day_user,peak_week_user,peak_month_user,inv_analysis,way_touch_it,ticket_sr,ticket_qi,urgency,complaint,praise,internal_knowledge_level,not_internal_knowledge_level,target_group,reading_times,reading_source,manager_itcode,VIP,Ecc_freq,Ecc_system_logo,ecc_peak_day_user,ecc_peak_week_user,ecc_peak_month_user,ecc_tcode,is_cip,is_csp,is_cgp,is_srm_ebp,is_srm_sus,is_pp,incident_peak_day_user,incident_peak_week_user,incident_peak_month_user,favorite_iteam,l0_browsing_category,srm_ebp_freq,srm_sus_freq,pp_freq,bw_freq,gts_freq,gts_key_t_code,srm_ebp_day_operation_peak,srm_ebp_week_operation_peak,srm_ebp_month_operation_peak,srm_sus_day_operation_peak,srm_sus_week_operation_peak,srm_sus_month_operation_peak,pp_day_operation_peak,pp_week_operation_peak,pp_month_operation_peak,bw_day_operation_peak,bw_week_operation_peak,bw_month_operation_peak,gts_day_operation_peak,gts_week_operation_peak,gts_month_operation_peak,incident_trend)"
                    + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt = con.prepareStatement(sql);

            pstmt.clearBatch();
            for (int i = 0; i < userlist.size(); i++) {
                UserProfile item = userlist.get(i);
                pstmt.setString(1,item.getUser_name());
                pstmt.setString(2,item.getEmployee_type());
                pstmt.setString(3,item.getWorking_years());
                pstmt.setString(4,item.getBand());
                pstmt.setString(5,item.getCountr());
                pstmt.setString(6,item.getCity());
                pstmt.setString(7,item.getBu());
                pstmt.setString(8,item.getPcg_dcg());
                pstmt.setString(9,item.getSegment());
                pstmt.setString(10,item.getIs_new_user());
                pstmt.setString(11,item.getTitle_change());
                pstmt.setString(12,item.getManager());
                pstmt.setString(13,item.getIs_rol_user());
                pstmt.setString(14,item.getIs_monthly_user());
                pstmt.setString(15,item.getKpi_keyword());
                pstmt.setString(16,item.getAsset_name());
                pstmt.setString(17,item.getFunction());
                pstmt.setString(18,item.getApp_ticket_num());
                pstmt.setString(19,item.getNot_app_ticket_num());
                pstmt.setString(20,item.getTicket_type());
                pstmt.setString(21,item.getTicket_distribution());
                pstmt.setString(22,item.getPeak_day_user());
                pstmt.setString(23,item.getPeak_week_user());
                pstmt.setString(24,item.getPeak_month_user());
                pstmt.setString(25,item.getInv_analysis());
                pstmt.setString(26,item.getWay_touch_it());
                pstmt.setString(27,item.getTicket_sr());
                pstmt.setString(28,item.getTicket_qi());
                pstmt.setString(29,item.getUrgency());
                pstmt.setString(30,item.getComplaint());
                pstmt.setString(31,item.getPraise());
                pstmt.setString(32,item.getInternal_knowledge_level());
                pstmt.setString(33,item.getNot_internal_knowledge_level());
                pstmt.setString(34,item.getTarget_group());
                pstmt.setString(35,item.getReading_times());
                pstmt.setString(36,item.getReading_source());
                pstmt.setString(37,item.getManager_itcode());
                pstmt.setString(38,item.getVIP());
                pstmt.setString(39,item.getEcc_freq());
                pstmt.setString(40,item.getEcc_system_logo());
                pstmt.setString(41,item.getEcc_day_operation_peak());
                pstmt.setString(42,item.getEcc_week_operation_peak());
                pstmt.setString(43,item.getEcc_month_operation_peak());
                pstmt.setString(44,item.getKey_t_code());
                pstmt.setString(45,item.getCIP_system_logo());
                pstmt.setString(46,item.getCSP_system_logo());
                pstmt.setString(47,item.getCGP_system_logo());
                pstmt.setString(48,item.getSRM_EBP_system_logo());
                pstmt.setString(49,item.getSRM_SUS_system_logo());
                pstmt.setString(50,item.getPP_system_logo());
                pstmt.setString(51,item.getCN_peak_day_user());
                pstmt.setString(52,item.getCN_peak_week_user());
                pstmt.setString(53,item.getCN_peak_month_user());
                pstmt.setString(54,item.getAssgind_group());
                pstmt.setString(55,item.getDoc_type());

                //10/17
                pstmt.setString(56,item.getSRM_EBP_freq());
                pstmt.setString(57,item.getSRM_SUS_freq());
                pstmt.setString(58,item.getPP_freq());
                pstmt.setString(59,item.getBW_freq());
                pstmt.setString(60,item.getGTS_freq());
                pstmt.setString(61,item.getGts_key_t_code());

                pstmt.setString(62,item.getSrm_ebp_day_operation_peak());
                pstmt.setString(63,item.getSrm_ebp_week_operation_peak());
                pstmt.setString(64,item.getSrm_ebp_month_operation_peak());

                pstmt.setString(65,item.getSrm_sus_day_operation_peak());
                pstmt.setString(66,item.getSrm_sus_week_operation_peak());
                pstmt.setString(67,item.getSrm_sus_month_operation_peak());

                pstmt.setString(68,item.getPp_day_operation_peak());
                pstmt.setString(69,item.getPp_week_operation_peak());
                pstmt.setString(70,item.getPp_month_operation_peak());

                pstmt.setString(71,item.getBw_day_operation_peak());
                pstmt.setString(72,item.getBw_week_operation_peak());
                pstmt.setString(73,item.getBw_month_operation_peak());

                pstmt.setString(74,item.getGts_day_operation_peak());
                pstmt.setString(75,item.getGts_week_operation_peak());
                pstmt.setString(76,item.getGts_month_operation_peak());
                pstmt.setString(77,item.getTicket_trend());

                pstmt.addBatch();
            }
            pstmt.executeBatch();
            System.out.println("Sava Success!!!");

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 列出表
     *
     * @param admin
     * @throws IOException
     */
    private void listTables(Admin admin) throws IOException {
        TableName[] names = admin.listTableNames();
        for (TableName tableName : names) {
            HBaseManagerMain.LOG.info("Table Name is : " + tableName.getNameAsString());
            System.out.println(tableName.getNameAsString());
        }
    }


    /**
     * 创建表
     *
     * @param admin
     * @throws IOException
     */
    private void createTable(Admin admin) throws IOException {
        TableName tableName = TableName.valueOf(HBaseManagerMain.TABLE_NAME);
        HBaseManagerMain.LOG.info("To create table named " + HBaseManagerMain.TABLE_NAME);
        HTableDescriptor tableDesc = new HTableDescriptor(tableName);
        HColumnDescriptor columnDesc = new HColumnDescriptor(HBaseManagerMain.COLUMN_FAMILY_NAME);
        tableDesc.addFamily(columnDesc);

        admin.createTable(tableDesc);
    }

    /**
     * 检索数据-表扫描
     *
     * @param connection
     * @return
     * @throws IOException
     */
    private List<UserProfile> scanTable(Connection connection) throws IOException {
        HBaseManagerMain.LOG.info("Scan table " + HBaseManagerMain.TABLE_NAME + " to browse all datas.");
        List<UserProfile> us = new ArrayList<UserProfile>();
        TableName tableName = TableName.valueOf(HBaseManagerMain.TABLE_NAME);
        //byte[] family = Bytes.toBytes(HBaseManagerMain.COLUMN_FAMILY_NAME);
        Scan scan = new Scan();
        //scan.addFamily(family);
        Table table = connection.getTable(tableName);
        ResultScanner resultScanner = table.getScanner(scan);
        for (Result result : resultScanner) {
            List<Cell> cells = result.listCells();
            UserProfile u = new UserProfile();
            u.setUser_name(Bytes.toString(result.getRow()));
            for (Cell cell : cells) {
                String qualifier = Bytes.toString(CellUtil.cloneQualifier(cell));
                //String value = Bytes.toString(CellUtil.cloneValue(cell));

                String value = new String(Bytes.toString(CellUtil.cloneValue(cell)).getBytes(), "UTF-8");
                HBaseManagerMain.LOG.info(qualifier + ":" + value);
                if("user_name".equals(qualifier)) {
                    u.setUser_name(value );
                } else if("employee_type".equals(qualifier)) {
                    u.setEmployee_type(value );
                } else if("working_years".equals(qualifier)) {
                    u.setWorking_years(value);
                } else if("band".equals(qualifier)) {
                    u.setBand(value);
                } else if("country".equals(qualifier)) {
                    u.setCountr(value);
                } else if("city".equals(qualifier)) {
                    u.setCity(value);
                } else if("bu".equals(qualifier)) {
                    u.setBu(value);
                } else if("pcg_dcg".equals(qualifier)) {
                    u.setPcg_dcg(value);
                } else if("segment".equals(qualifier)) {
                    u.setSegment(value);
                } else if("is_new_user".equals(qualifier)) {
                    u.setIs_new_user(value);
                } else if("title_change".equals(qualifier)) {
                    u.setTitle_change(value);
                } else if("manager".equals(qualifier)) {
                    u.setManager(value);
                } else if("is_rol_user".equals(qualifier)) {
                    u.setIs_rol_user(value);
                } else if("is_monthly_user".equals(qualifier)) {
                    u.setIs_monthly_user(value);
                } else if("kpi_keyword".equals(qualifier)) {
                    u.setKpi_keyword(value);
                } else if("asset_name".equals(qualifier)) {
                    u.setAsset_name(value);
                } else if("function".equals(qualifier)) {
                    u.setFunction(value);
                } else if("app_ticket_num".equals(qualifier)) {
                    u.setApp_ticket_num(value);
                } else if("not_app_ticket_num".equals(qualifier)) {
                    u.setNot_app_ticket_num(value);
                } else if("ticket_type".equals(qualifier)) {
                    u.setTicket_type(value);
                } else if("ticket_distribution".equals(qualifier)) {
                    u.setTicket_distribution(value);
                } else if("peak_day_user".equals(qualifier)) {
                    u.setPeak_day_user(value);
                } else if("peak_week_user".equals(qualifier)) {
                    u.setPeak_week_user(value);
                } else if("peak_month_user".equals(qualifier)) {
                    u.setPeak_month_user(value);
                } else if("inv_analysis".equals(qualifier)) {
                    u.setInv_analysis(value);
                } else if("way_touch_it".equals(qualifier)) {
                    u.setWay_touch_it(value);
                } else if("ticket_sr".equals(qualifier)) {
                    u.setTicket_sr(value);
                } else if("ticket_qi".equals(qualifier)) {
                    u.setTicket_qi(value);
                } else if("urgency".equals(qualifier)) {
                    u.setUrgency(value);
                } else if("complaint".equals(qualifier)) {
                    u.setComplaint(value);
                } else if("praise".equals(qualifier)) {
                    u.setPraise(value);
                } else if("internal_knowledge_level".equals(qualifier)) {
                    u.setInternal_knowledge_level(value);
                } else if("not_internal_knowledge_level".equals(qualifier)) {
                    u.setNot_internal_knowledge_level(value);
                } else if("target_group".equals(qualifier)) {
                    u.setTarget_group(value);
                } else if("reading_times".equals(qualifier)) {
                    u.setReading_times(value);
                } else if("reading_source".equals(qualifier)) {
                    u.setReading_source(value);
                }else if("manager_itcode".equals(qualifier)) {
                    u.setManager_itcode(value);
                }else if("vip".equals(qualifier)) {
                    u.setVIP(value);
                }else if("ECC_freq".equals(qualifier)) {
                    u.setEcc_freq(value);
                }else if("ECC_system_logo".equals(qualifier)) {
                    u.setEcc_system_logo(value);
                } else if("CIP_system_logo".equals(qualifier)) {
                    u.setCIP_system_logo(value);
                } else if("CSP_system_logo".equals(qualifier)) {
                    u.setCSP_system_logo(value);
                } else if("CGP_system_logo".equals(qualifier)) {
                    u.setCGP_system_logo(value);
                } else if("SRM_EBP_system_logo".equals(qualifier)) {
                    u.setSRM_EBP_system_logo(value);
                } else if("SRM_SUS_system_logo".equals(qualifier)) {
                    u.setSRM_SUS_system_logo(value);
                }else if("PP_system_logo".equals(qualifier)) {
                    u.setPP_system_logo(value);
                }else if("CN_peak_day_user".equals(qualifier)) {
                    u.setCN_peak_day_user(value);
                }else if("CN_peak_week_user".equals(qualifier)) {
                    u.setCN_peak_week_user(value);
                }else if("CN_peak_month_user".equals(qualifier)) {
                    u.setCN_peak_month_user(value);
                }else if("assgind_group".equals(qualifier)) {
                    u.setAssgind_group(value);
                }else if("doc_type".equals(qualifier)) {
                    u.setDoc_type(value);
                }


                  else if("SRM_EBP_freq".equals(qualifier)) {
                    u.setSRM_EBP_freq(value);
                } else if("SRM_SUS_freq".equals(qualifier)) {
                    u.setSRM_SUS_freq(value);
                } else if("PP_freq".equals(qualifier)) {
                    u.setPP_freq(value);
                } else if("BW_freq".equals(qualifier)) {
                    u.setBW_freq(value);
                } else if("GTS_freq".equals(qualifier)) {
                    u.setGTS_freq(value);
                }
                  else if("ecc_key_t_code".equals(qualifier)) {
                    u.setKey_t_code(value);
                } else if("gts_key_t_code".equals(qualifier)) {
                    u.setGts_key_t_code(value);
                }
                  else if("ecc_day_operation_peak".equals(qualifier)) {
                    u.setEcc_day_operation_peak(value);
                } else if("ecc_week_operation_peak".equals(qualifier)) {
                    u.setEcc_week_operation_peak(value);
                } else if("ecc_month_operation_peak".equals(qualifier)) {
                    u.setEcc_month_operation_peak(value);
                }
                   else if("srm_ebp_day_operation_peak".equals(qualifier)) {
                    u.setSrm_ebp_day_operation_peak(value);
                } else if("srm_ebp_week_operation_peak".equals(qualifier)) {
                    u.setSrm_ebp_week_operation_peak(value);
                } else if("srm_ebp_month_operation_peak".equals(qualifier)) {
                    u.setSrm_ebp_month_operation_peak(value);
                }
                  else if("srm_sus_day_operation_peak".equals(qualifier)) {
                    u.setSrm_sus_day_operation_peak(value);
                } else if("srm_sus_week_operation_peak".equals(qualifier)) {
                    u.setSrm_sus_week_operation_peak(value);
                } else if("srm_sus_month_operation_peak".equals(qualifier)) {
                    u.setSrm_sus_month_operation_peak(value);
                } else if("pp_day_operation_peak".equals(qualifier)) {
                    u.setPp_day_operation_peak(value);
                } else if("pp_week_operation_peak".equals(qualifier)) {
                    u.setPp_week_operation_peak(value);
                } else if("pp_month_operation_peak".equals(qualifier)) {
                    u.setPp_month_operation_peak(value);
                } else if("bw_day_operation_peak".equals(qualifier)) {
                    u.setBw_day_operation_peak(value);
                } else if("bw_week_operation_peak".equals(qualifier)) {
                    u.setBw_week_operation_peak(value);
                } else if("bw_month_operation_peak".equals(qualifier)) {
                    u.setBw_month_operation_peak(value);
                } else if("gts_day_operation_peak".equals(qualifier)) {
                    u.setGts_day_operation_peak(value);
                } else if("gts_week_operation_peak".equals(qualifier)) {
                    u.setGts_week_operation_peak(value);
                } else if("gts_month_operation_peak".equals(qualifier)) {
                    u.setGts_month_operation_peak(value);
                } else if("ticket_trend".equals(qualifier)) {
                    u.setTicket_trend(value);
                }
            }
            if (u.getUser_name() != null && !"".equals(u.getUser_name())
                    && !"NULL".equals(u.getUser_name())) {
                us.add(u);
            }
        }

        return us;
    }

}